Rename Schema

If your transformation type is Rename Schema, you can do the following:

• Rename schema to (string)
• Add a prefix or suffix
• Remove a prefix or suffix
• Replace a prefix or suffix with different characters
• Convert schema name to uppercase
• Convert schema name to lowercase
• Rename schema (Expression)

Rename schema to (string)

Use the Rename schema to: [string] option to change the name of all table schemas that you defined in the Transformation scope step to a different name. For example, if you have a schema called Human_Resources and want to change all instances of this name to HR then enter the string HR. You can enter any string in this field.

Add a prefix or suffix

Use the Add a prefix or suffix option to add additional characters to the beginning or end of the schema name for all schemas that fit the definition you created in the Transformation scope step. For example, if the schema name is HR, you can add a suffix, such as TAR or _TAR to the schema name for all tables with that schema name. In this case, the resulting schema name will be HRTAR or HR_TAR.

To globally add a prefix or suffix

1. Select Add <Prefix/Suffix> Insert Characters to matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want as the prefix or suffix. If you want to include an underscore or other legal character to separate the prefix/suffix from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Remove a prefix or suffix

Use the Remove a prefix or suffix option to remove a string of characters from the beginning or end of a schema name for all schema that fit the definition you created in the Transformation scope step.

For example, you can use this option to remove the letters _REV from the schema name for all tables in the schema HR_REV. In this case the schema name in the target will be HR.

To globally remove a prefix or suffix

1. Select Remove <Prefix/Suffix> Insert Characters from matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click [string] to activate the field.
4. Type the characters you want to remove. If you want to remove an underscore or other legal character from the original name, you must add it as part of the character string.
5. Click Finish to add the rule to the Global Rules list.

Replace a prefix or suffix with different characters

Use the Replace a prefix or suffix option to replace a string of characters with a different string of characters. You determine whether to replace the characters at the beginning or end of a schema name for all schema that fit the definition you created in the Transformation scope step.

For example, you can use this option to replace the letters _ORIG with _REPL in the schema name for all tables in the schema HR_ORIG. In this case the schema name in the target will be HR_REPL.

To globally replace a prefix or suffix

1. Select Replace <Prefix/Suffix> Insert Characters by Insert Characters for all matching schema names.
2. Click the word Prefix or Suffix and select one of these two from the list.
3. Click the first [string] to activate the field.
4. Type the characters from the existing (source) schema that you want to replace. If you want to include an underscore or other legal character from the original name in the string that you want to replace, you must add it as part of the character string.
5. Click the second [string] to activate the field.
6. Type the characters you want to use in the target. These characters replace the original (source) characters in the target.
7. Click Finish to add the rule to the Global Rules list.

Convert schema name to uppercase

Use the convert to uppercase option to convert all of the letters in a schema name to upper case. For example:

• Schema_cat, becomes SCHEMA_CAT
• schema_cat, becomes SCHEMA_CAT
• sChEMa_Cat, becomes SCHEMA_CAT

To globally change the schema name to all uppercase

1. Select Convert schema name to uppercase.
2. Click Finish to add the rule to the Global Rules list.

Convert schema name to lowercase

Use the convert to lowercase option to convert all of the letters in a schema name to lower case. For example:

• Schema_cat, becomes schema_cat
• SCHEMA_CAT, becomes schema_cat
• sChEMa_Cat, becomes schema_cat

To globally change the schema name to all uppercase

1. Select Convert schema name to lowercase.
2. Click Finish to add the rule to the Global Rules list.

Rename schema (Expression)

Use the Rename schema to [expression] option to change the name of all table schemas that you defined in the Transformation scope step to a different name. For example, if you have a schema called Human_Resources and want to change all instances of this name to HR.

To globally change a schema name

1. Select Rename schema to [expression]

2. Click the button to the right of the Rename schema option to open the Expression Editor. For information on how to use the Expression Editor, see Using the Expression Builder. Then go to step 4.

   or

   Click [expression] to activate the field and continue with step 3.

3. Type an SQLite expression or a string (in quotes) to rename the schema. For example:

   • "New_Schema"
   • ’PREF_’||$SCHEMA_NAME_VAR||’_SUFF’

   You can use the following variables in the SQLite expression:

   • $SCHEMA_NAME_VAR
   • $TABLE_NAME_VAR
   • $COLUMN_NAME_VAR
   • $COLUMN_DATATYPE_VAR
4. Click Finish to add the rule to the Global Rules list.